package com.ylwl.datacenterdemo.service.impl;

import com.ylwl.datacenterdemo.entity.ExcelHeader;
import com.ylwl.datacenterdemo.entity.TableMetadata;
import com.ylwl.datacenterdemo.service.TableStructureManager;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.util.List;

/**
 * 数据库表结构管理器实现类
 */
@Slf4j
@Service
@RequiredArgsConstructor
public class DatabaseTableStructureManager implements TableStructureManager {
    
    private final JdbcTemplate jdbcTemplate;
    
    @Override
    public boolean createTableStructure(TableMetadata metadata) {
        try {
            String tableName = metadata.getTableName();
            List<ExcelHeader> headers = metadata.getHeaders();
            
            // 检查表是否已存在
            if (!doesTableExist(tableName)) {
                return createNewTable(metadata);
            } else {
                log.info("表 {} 已存在，检查是否需要添加row_index字段", tableName);
                // 表已存在，检查是否包含row_index字段
                if (!doesColumnExist(tableName, "row_index")) {
                    // 添加row_index字段
                    String addColumnSql = "ALTER TABLE `" + tableName + "` ADD COLUMN `row_index` INT COMMENT 'Excel行号'";
                    jdbcTemplate.execute(addColumnSql);
                    log.info("为表 {} 添加了row_index字段", tableName);
                } else {
                    log.info("表 {} 已包含row_index字段", tableName);
                }
                return true;
            }
        } catch (Exception e) {
            log.error("创建表 {} 时出错: ", metadata.getTableName(), e);
            throw new RuntimeException("创建表失败: " + metadata.getTableName(), e);
        }
    }
    
    /**
     * 创建新表
     * @param metadata 表元数据
     * @return 是否创建成功
     */
    private boolean createNewTable(TableMetadata metadata) {
        try {
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.append("CREATE TABLE `").append(metadata.getTableName()).append("` (");
            sqlBuilder.append("id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键', ");
            // 添加行号字段，用于记录Excel中的行号
            sqlBuilder.append("row_index INT COMMENT 'Excel行号', ");
            
            // 根据表头添加字段
            for (ExcelHeader header : metadata.getHeaders()) {
                if (header.isValid() && header.getFieldName() != null) {
                    // 添加字段，类型为VARCHAR(1000)，添加注释
                    sqlBuilder.append("`").append(header.getFieldName())
                            .append("` VARCHAR(1000) COMMENT '")
                            .append(header.getName().replace("'", "''")) // 转义单引号
                            .append("', ");
                }
            }
            
            sqlBuilder.append("create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', ");
            sqlBuilder.append("PRIMARY KEY (id)");
            sqlBuilder.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='动态表_").append(metadata.getTableName()).append("'");
            
            jdbcTemplate.execute(sqlBuilder.toString());
            log.info("成功创建表: {}，包含 {} 个字段", metadata.getTableName(), metadata.getValidHeaderCount());
            return true;
        } catch (Exception e) {
            log.error("创建新表 {} 时出错: ", metadata.getTableName(), e);
            return false;
        }
    }
    
    @Override
    public boolean doesTableExist(String tableName) {
        try (Connection connection = jdbcTemplate.getDataSource().getConnection()) {
            DatabaseMetaData meta = connection.getMetaData();
            ResultSet resultSet = meta.getTables(connection.getCatalog(), null, tableName, new String[] {"TABLE"});
            return resultSet.next();
        } catch (Exception e) {
            log.error("检查表 {} 是否存在时出错: ", tableName, e);
            return false;
        }
    }
    
    @Override
    public boolean doesColumnExist(String tableName, String columnName) {
        try (Connection connection = jdbcTemplate.getDataSource().getConnection()) {
            DatabaseMetaData meta = connection.getMetaData();
            ResultSet resultSet = meta.getColumns(connection.getCatalog(), null, tableName, columnName);
            return resultSet.next();
        } catch (Exception e) {
            log.error("检查表 {} 中字段 {} 是否存在时出错: ", tableName, columnName, e);
            return false;
        }
    }
}